package com.basic.datapersist;

import java.io.Reader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import org.apache.log4j.Logger;

import com.basic.common.FuncDate;
import com.basic.common.FuncStatic;

public class DbBasicService implements NeedConnect {
	private static Logger logger = Logger.getLogger(DbBasicService.class);
	private ConditionTool condtionTool = null;
	private DbConnectTool dbConnect = null;
	private MapTable table = null;
	private HashMap<String, String> newData = null;
	private Connection conn = null;
	private List<Statement> listStatement = new ArrayList<Statement>();

	protected DbBasicService() {
	}

	public void setConditionTool(ConditionTool tool) {
		this.condtionTool = tool;
	}

	@Override
	public void initConnect(DbConnectTool connect) {
		this.dbConnect = connect;
		this.conn = this.dbConnect.getConnection();
	}

	/** @param table */
	public void setMapTable(MapTable table) {
		this.table = table;
	}

	public void commit() throws Exception {
		try {
			if (conn != null) {
				conn.commit();
			}
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				logger.error(FuncStatic.errorTrace(e1));
			}
			throw e;
		}
	}

	/** @param value */
	public void setNewData(HashMap<String, String> value) {
		newData = value;
	}

	public String queryCreateSql() {
		StringBuilder sql = new StringBuilder();
		sql.append("select ");
		sql.append(table.getSelectColumns());
		sql.append(" from ");
		sql.append(table.getTableName());
		if (this.condtionTool != null && this.condtionTool.hasCondition()) {
			sql.append(" where ");
			sql.append(this.getConditionStr());
		}
		sql.append(" order by ");
		sql.append(this.orderStr());

		String querySql = sql.toString();
		if (condtionTool.getReadLength() != 0 || condtionTool.getStartIndex() != 0) {
			querySql = this.dbConnect.formatPagerSql(querySql,
					condtionTool.getStartIndex(), condtionTool.getReadLength());
		}
		return querySql;
	}

	private void freeResult(ResultSet result) {
		try {
			if (result != null) {
				result.close();
			}
		} catch (Exception e) {
			logger.error(FuncStatic.errorTrace(e));
		}
	}

	public List<HashMap<String, String>> queryExec() throws Exception {
		String[] cs = table.getSelectColumns().split(",");
		String sql = this.queryCreateSql();
		List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		ResultSet resultSet = queryExecResultSet(sql);
		try {
			while (resultSet.next()) {
				HashMap<String, String> map = new HashMap<String, String>();
				for (String column : cs) {
					if (resultSet.getObject(column) != null) {
						if (table.isDateColumn(column))
							map.put(column, FuncDate.dateToString(FuncDate
									.formatToDate(resultSet
											.getTimestamp(column))));
						else
							map.put(column, resultSet.getObject(column)
									.toString());
					}
				}
				list.add(map);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			freeResult(resultSet);
		}
		return list;
	}

	public long queryExecCount() throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select count(*) as c from ");
		sql.append(table.getTableName());
		if (this.condtionTool != null && this.condtionTool.hasCondition()) {
			sql.append(" where ");
			sql.append(this.getConditionStr());
		}
		String querySql = sql.toString();
		loggerSql(querySql);

		ResultSet resultSet = null;
		try {
			Statement stmt = null;
			stmt = conn.createStatement();
			listStatement.add(stmt);
			resultSet = stmt.executeQuery(querySql);
			resultSet.next();
			return resultSet.getLong(1);
		} catch (Exception e) {
			throw e;
		} finally {
			freeResult(resultSet);
		}
	}

	/** @param sql */
	public ResultSet queryExecResultSet(String sql) throws Exception {
		loggerSql(sql);
		Statement stmt = conn.createStatement();
		listStatement.add(stmt);
		return stmt.executeQuery(sql.toString());
	}

	public void freeResource() {
		try {
			for (Statement stat : listStatement) {
				if (stat != null) {
					stat.close();
				}
			}
			if (this.conn != null) {
				conn.close();
			}
		} catch (Exception e) {
			logger.error(FuncStatic.errorTrace(e));
		}
	}

	/** @param columnName */
	public StringBuilder readTextColumn(String columnName) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select " + columnName + " from ");
		sql.append(table.getTableName());
		if (this.condtionTool != null && this.condtionTool.hasCondition()) {
			sql.append(" where ");
			sql.append(this.getConditionStr());
		}
		String querySql = sql.toString();
		loggerSql(querySql);

		StringBuilder sb = new StringBuilder();
		ResultSet resultSet = null;
		Statement stmt = null;
		stmt = conn.createStatement();
		listStatement.add(stmt);
		resultSet = stmt.executeQuery(sql.toString());
		try {
			if (resultSet.next()) {
				Clob c = resultSet.getClob(columnName);
				if (c != null) {
					Reader reader = c.getCharacterStream();
					char[] charbuf = new char[4096];
					for (int i = reader.read(charbuf); i > 0; i = reader
							.read(charbuf)) {
						sb.append(charbuf, 0, i);
					}
				}
			}
		} catch (Exception e) {
			throw e;
		} finally {
			freeResult(resultSet);
		}
		return sb;
	}

	public String updateCreateSql() {
		StringBuilder sql = new StringBuilder();
		sql.append("update ");
		sql.append(table.getTableName());
		sql.append(" set ");
		for (String column : newData.keySet()) {
			sql.append(column);
			sql.append("=");
			sql.append(this.formatSqlValue(newData.get(column)));
			sql.append(",");
		}
		sql.deleteCharAt(sql.length() - 1);
		if (this.condtionTool != null && this.condtionTool.hasCondition()) {
			sql.append(" where ");
			sql.append(this.getConditionStr());
		}
		 
		return sql.toString();
	}

	public int updateExec() throws Exception {
		String sql = updateCreateSql();
		loggerSql(sql);

		Statement stmt = conn.createStatement();
		listStatement.add(stmt);
		return stmt.executeUpdate(sql);
	}

	public String insertCreateSql() {
		StringBuilder sql = new StringBuilder();
		sql.append("insert into ");
		sql.append(table.getTableName());
		sql.append(" (");
		for (String column : newData.keySet()) {
			sql.append(column);
			sql.append(",");
		}
		sql.deleteCharAt(sql.length() - 1);
		sql.append(") values (");
		for (String column : newData.keySet()) {
			sql.append(this.formatSqlValue(newData.get(column)));
			sql.append(",");
		}
		sql.deleteCharAt(sql.length() - 1);
		sql.append(")");
		return sql.toString();
	}

	public void insertExec() throws Exception {
		String sql = insertCreateSql();
		loggerSql(sql);

		Statement stmt = conn.createStatement();
		listStatement.add(stmt);
		stmt.executeUpdate(sql);
	}

	public String insertExecReauto() throws Exception {
		String sql = insertCreateSql();
		loggerSql(sql);

		String reid = null;
		Statement stmt = conn.createStatement();
		listStatement.add(stmt);
		stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
		if (stmt.getGeneratedKeys().next()) {
			ResultSet rs = stmt.getGeneratedKeys();
			try {
				if (rs.next()) {
					reid = rs.getObject(1).toString();
				}
			} catch (Exception e) {
				throw e;
			} finally {
				this.freeResult(rs);
			}
		}
		return reid;
	}

	public String deleteCreateSql() throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append(" delete from ");
		sql.append(this.table.getTableName());
		if (this.condtionTool != null && this.condtionTool.hasCondition()) {
			sql.append(" where ");
			sql.append(this.getConditionStr());
		}
		return sql.toString();
	}

	public int deleteExec() throws Exception {
		String sql = deleteCreateSql();
		loggerSql(sql);

		Statement stmt = conn.createStatement();
		listStatement.add(stmt);
		return stmt.executeUpdate(sql);
	}

	/** @param sql */
	public int execSql(String sql) throws Exception {
		Statement stmt = conn.createStatement();
		listStatement.add(stmt);
		return stmt.executeUpdate(sql);
	}

	private String formatSqlValue(String value) {
		return "'" + value.toString().replaceAll("'", "''") + "'";
	}

	private String getConditionOperator(ConditionOperator conditionOperatorEnum) {
		String o = null;
		switch (conditionOperatorEnum) {
		case EQ:
			o = " = ";
			break;
		case GE:
			o = " >= ";
			break;
		case GT:
			o = " > ";
			break;
		case LE:
			o = " <= ";
			break;
		case LT:
			o = " < ";
			break;
		case NOT:
			o = " != ";
			break;
		case LIKE:
			o = " like ";
			break;
		case IS:
			o = " is ";
			break;
		case IS_NOT:
			o = " is not ";
			break;
		case IN:
			o = " in ";
			break;
		}
		return o;
	}

	private void loggerSql(String sql) {
		if (this.dbConnect.printSql()) {
			if (sql.length() > 800) {
				logger.info(sql.subSequence(0, 800));
			} else {
				logger.info(sql.toString());
			}
		}
	}

	private String getConditionStr() {
		StringBuilder str = new StringBuilder();
		if (this.condtionTool != null) {
			for (AndCondition ac : condtionTool.getListCondition()) {
				str.append(ac.column);
				str.append(this.getConditionOperator(ac.operator));
				if (ac.operator == ConditionOperator.IS
						|| ac.operator == ConditionOperator.IS_NOT
						|| ac.operator == ConditionOperator.IN)
					str.append(ac.value);
				else
					str.append(this.formatSqlValue(ac.value));
				str.append(" and ");
			}

			for (OrCondition oc : condtionTool.getListOrCondition()) {
				str.append("(");
				for (int i = 0; i < oc.column.size(); i++) {
					str.append(oc.column.get(i));
					str.append(this.getConditionOperator(oc.operator.get(i)));
					if (oc.operator.get(i) == ConditionOperator.IS
							|| oc.operator.get(i) == ConditionOperator.IS_NOT
							|| oc.operator.get(i) == ConditionOperator.IN)
						str.append(oc.value.get(i));
					else
						str.append(this.formatSqlValue(oc.value.get(i)));
					str.append(" or ");
				}
				str.delete(str.length() - 4, str.length());
				str.append(")");
				str.append(" and ");
			}
		}

		if (str.length() > 5) {
			return str.substring(0, str.length() - 5);
		}
		return null;
	}

	private String orderStr() {
		if (FuncStatic.checkIsEmpty(condtionTool.getOrderStr())  )
			return table.getKeyColumns();
		else
			return condtionTool.getOrderStr();
	}
}
